/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Utilitarios.ConectarMySQL;
import Modelo.Despacho;
import java.sql.*;
import java.util.ArrayList;

/**
 * @author everson
 */
public class DAODespacho {
    
    private ConectarMySQL co = ConectarMySQL.getInstance();
    private String sql = "";

    public DAODespacho(ConectarMySQL conDespacho) {
        this.co = conDespacho;
    }

    //Medoto para listar todas as Cidades
    public ArrayList list() throws SQLException {
    ArrayList list = new ArrayList();
    Connection conDespacho = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
      conDespacho = co.getConexao();
      String sql = "SELECT ID,DESCRICAO from DESPACHOS order by DESCRICAO";

      pstmt = conDespacho.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while(rs.next()) {
        Despacho s = new Despacho();
        s.setId(rs.getInt(1));
        s.setDescricao(rs.getString(2));
        list.add(s);
      }
      rs.close();
      pstmt.close();
    } catch (SQLException e) {
      rs.close();
      pstmt.close();
      conDespacho.rollback();
      e.printStackTrace();
    } finally {
    	co.desConecta(conDespacho);
    }
    return list;
  }
    
    public int insere(Despacho despacho) throws SQLException {
       PreparedStatement pstmt = null;
       Connection conDespacho = null;
       int a = 0;
       sql = "insert into DESPACHOS (DESCRICAO) values (?)";

       try {
       conDespacho = co.getConexao();
       pstmt = conDespacho.prepareStatement(sql);       
       pstmt.setString(1, despacho.getDescricao());
       
       a = pstmt.executeUpdate();
       pstmt.close();
       return a;
       } catch (SQLException sqle) {
         sqle.printStackTrace();
         throw sqle;
       } finally {
    	 co.desConecta(conDespacho);
       }
    }

    public int altera(Despacho despacho) throws SQLException {
       PreparedStatement pstmt = null;
       Connection conDespacho = null;
       int a = 0;
       sql = "update DESPACHOS set DESCRICAO = ? where ID = ?";

       try {
       conDespacho = co.getConexao();
       pstmt = conDespacho.prepareStatement(sql);       

       pstmt.setString(1, despacho.getDescricao());
       pstmt.setInt(2, despacho.getId());
       
       a = pstmt.executeUpdate();
       pstmt.close();
       return a;
       } catch (SQLException sqle) {
         sqle.printStackTrace();
         throw sqle;
       } finally {
    	 co.desConecta(conDespacho);
       }
    }

    public int exclui(Despacho despacho) throws SQLException {
       int a = 0;
       sql = "DELETE from DESPACHOS where ID = ?";
       Connection conDespacho = null;
       PreparedStatement pstmt = null;
       try {
          conDespacho = co.getConexao();
          pstmt = conDespacho.prepareStatement(sql);
          pstmt.setInt(1, despacho.getId());
          a = pstmt.executeUpdate();
          pstmt.close();
          return a;
       } catch (SQLException sqle) {
          sqle.printStackTrace();
          throw sqle;
       } finally {
    	  co.desConecta(conDespacho);
       }
    }
    
    public int pegaIdDespacho(String descricao) throws SQLException {
       int ret = 0;
       Connection conDespacho = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       
       try {       
          conDespacho = co.getConexao();
          sql = "select ID from DESPACHOS where DESCRICAO = '"+descricao+"'";
          
          pstmt = conDespacho.prepareStatement(sql);
          rs = pstmt.executeQuery();
          //rs.first();
          rs.next();
          ret = rs.getInt("ID");
       } catch (SQLException e) {
          pstmt.close();
          conDespacho.rollback();
          e.printStackTrace();
       } finally {
          rs.close();
          pstmt.close();
          co.desConecta(conDespacho);
       }
       return ret;
    }
    
}
